ALTER TABLE LvReo_MasterList
ADD EstimatedResale MONEY NOT NULL DEFAULT(0)
GO
ALTER PROCEDURE [dbo].[getLvreo_MasterList]
(
    @Apn          varchar(50) = '', --Apn Number
    @PropLocation varchar(400) = '', --Property Location
    @Zip          varchar(50) = '', --Zip
    @showAllUnassigned bit = 0,
    @PMId int = null
)

AS

BEGIN
          DECLARE @maxInt INT
          SET @maxInt = 2147483647

        SELECT m.PropertyId, m.ListingStatus, m.AuctionDate, m.TsNumber, m.Apn, m.ZipCode, m.Trustee, m.Site, 
               m.PropertyLocation, m.EstimatedBid, m.TsOpeningBid, m.ApnOpeningBid, m.vo, m.Hoa, m.MaxBid, 
               m.EstVal, m.Tax, m.Nos, m.Liens, m.OnOff, m.PropertyDetails, m.DocumentNumber, m.DateRecorded,
               p.LotSize, p.ConstructYear, p.LandUse, p.TotalSqft, p.GarageSqft,p.Stories, p.Bedrooms, p.Bathrooms,
               p.Pool, p.Spa, REPLACE(p.AssessorDesc, '<br/>', ' ') AS AssessorDesc, m.Phase,
               Client, CloseDate, EscrowCompany, MiscNotes, PrelistStatus, PurchasePrice, RehabCosts, Source,
               ua.Fname + ' ' + ua.LName as InvestorName, InvestorId,
               m.DateAccept, m.EstimateSalePrice, m.EstimatedResale
        FROM (SELECT * 
              FROM LvReo_MasterList 
              WHERE (Apn LIKE '%'+@Apn+'%')
              AND (isnull(PropertyLocation, '') LIKE '%'+@PropLocation+'%')
              AND (isnull(ZipCode, '') LIKE '%'+@Zip+'%')
              AND (isnull(PMId, -1) BETWEEN ISNULL(@PMId, -1) AND ISNULL(@PMId, @maxInt)))  AS m 
          LEFT JOIN LvReo_ParcellAppraisal AS p ON m.Apn = p.Apn 
        LEFT JOIN UserAccounts ua on m.InvestorId = ua.UserId
        where
               case
                    when @showAllUnassigned = 0 then 1
                    when (@showAllUnassigned = 1) and (ListingStatus = 'Unassigned') and (isnull(CloseDate, '') = '') then 1
                    else 0
               end = 1
        ORDER BY
               case when ISNULL(CloseDate, '') = '' then m.ZipCode else 'z' end

END
GO
ALTER PROCEDURE [dbo].[updateLvreo_MasterList]
(
    @PropertyId        int='', --Id
    @OnOff             varchar(50)='', --OnOff
    @Trustee           varchar(150)='', --Trustee
    @TsNumber          varchar(75)='', --TsNumber
    @Apn               varchar(50)='', --Apn Key 
    @PropertyLocation  varchar(400)='', --Prop Location  
    @ZipCode           varchar(50)='', --ZipCode 
    @VO                varchar(50)='', --vo 
    @HOA               varchar(50)='', --Hoa 
    @Stories           varchar(50)='', --Stories 
    @ConstructYear     varchar(50)='', --Const Year 
    @TotalSQft         varchar(50)='', --Total SQft 
    @LotSize           varchar(50)='', --LotSize 
    @Bedrooms          varchar(50)='', -- Bed 
    @Bathrooms         varchar(50)='', -- Bath 
    @GarageSqft        varchar(50)='', --GarageSqft 
    @Pool              varchar(50)='', --Pool 
    @Spa               varchar(50)='', --Spa 
    @AuctionDate       varchar(75)='', --Auction Date 
    @ListingStatus     varchar(50)='', --Listing Status 
    @EstimatedBid      varchar(50)='', --Estimated Bid 
    @TsOpeningBid      varchar(50)='', --Ts OpeningBid 
    @ApnOpeningBid     varchar(50)='', --ApnOpeningBid 
    @MaxBid            varchar(50)='', --Max Bid 
    @EstVal            varchar(50)='', --Estimate Value 
    @Tax               varchar(50)='', --Tax 
    @Nos               varchar(50)='', --Nos 
    @Liens             varchar(500)='', --Liens
    @Site              varchar(100)='', --Site 
    @PropertyDetails   varchar(2000)='', --Property Details
    @DocumentNumber    varchar(75)='', --Doc Number 
    @DateRecorded      varchar(75)='', -- Date Rec 
    @LandUse           varchar(50)='', -- Land Use
    @AssessorDesc      varchar(200)='' -- Assessor Desc
    ,@Phase            int,
	@Client            varchar(50)='',
	@CloseDate         varchar(50)='',
	@EscrowCompany     varchar(50)='',
	@MiscNotes         varchar(2000)='',
	@PrelistStatus     varchar(50)='',
	@PurchasePrice     varchar(50)='',
	@RehabCosts        varchar(50)='',
	@Source            varchar(50)='',
	@investorName		varchar(50)='',
	@DateAccept         varchar(50)='',
	@EstimateSalePrice MONEY = 0,
	@EstimatedResale MONEY = 0
)

AS

BEGIN
        set @OnOff = UPPER(@OnOff)
        set @Stories = UPPER(@Stories)

        UPDATE LvReo_ParcellAppraisal
        SET Stories = @Stories,
            ConstructYear = @ConstructYear,
            TotalSqft = @TotalSQft,
            LotSize = @LotSize,
            Bedrooms = @Bedrooms,
            Bathrooms = @Bathrooms,
            GarageSqft = @GarageSqft,
            Pool = @Pool,
            Spa = @Spa,
            LandUse = @LandUse,
            AssessorDesc = @AssessorDesc,
            Apn = @Apn
        WHERE Apn = @Apn
        
        if @@ROWCOUNT = 0
			insert into LvReo_ParcellAppraisal(Stories, ConstructYear, TotalSqft, LotSize, Bedrooms, Bathrooms,
				GarageSqft, Pool, Spa, LandUse, AssessorDesc, Apn)
			values(@Stories, @ConstructYear, @TotalSqft, @LotSize, @Bedrooms, @Bathrooms,
				@GarageSqft, @Pool, @Spa, @LandUse, @AssessorDesc, @Apn)

        UPDATE LvReo_MasterList
        SET OnOff = @OnOff,
			TsNumber = @TsNumber,
            Trustee = @Trustee,
            PropertyLocation = @PropertyLocation,
            ZipCode = @ZipCode,
            vo = @VO,
            Hoa = @HOA,
            AuctionDate = @AuctionDate,
            ListingStatus = case when ISNULL(@investorName, '') <> '' then 'Reserved' else @ListingStatus end,
            EstimatedBid = @EstimatedBid,
            TsOpeningBid = @TsOpeningBid,
            ApnOpeningBid = @ApnOpeningBid,
            MaxBid = @MaxBid,
            EstVal = @EstVal,
            Tax = @Tax,
            Nos = @Nos,
            Liens = @Liens,
            Site = @Site,
            PropertyDetails = @PropertyDetails,
            DocumentNumber = @DocumentNumber,
            DateRecorded = @DateRecorded,
            Phase = @Phase,
			Client = @Client,
			CloseDate = @CloseDate,
			EscrowCompany = @EscrowCompany,
			MiscNotes = @MiscNotes,
			PrelistStatus = @PrelistStatus,
			PurchasePrice = @PurchasePrice,
			RehabCosts = @RehabCosts,
			Source = @Source,
			Apn = @Apn,
			InvestorId = @investorName,
			DateAccept = @DateAccept,
			EstimateSalePrice = @EstimateSalePrice,
			EstimatedResale = @EstimatedResale
        WHERE PropertyId = @PropertyId
END
GO
ALTER PROCEDURE [dbo].[insertLvreo_MasterList]
(
    @OnOff              varchar(50)='', --OnOff
    @Trustee            varchar(150)='', --Trustee
    @TsNumber           varchar(75)='', --TsNumber
    @Apn                varchar(50)='', --Apn Key 
    @PropertyLocation   varchar(400)='', --Prop Location  
    @ZipCode            varchar(50)='', --ZipCode 
    @VO                 varchar(50)='', --vo 
    @HOA                varchar(50)='', --Hoa 
    @Stories            varchar(50)='', --Stories 
    @ConstructYear      varchar(50)='', --Const Year 
    @TotalSQft          varchar(50)='', --Total SQft 
    @LotSize            varchar(50)='', --LotSize 
    @Bedrooms           varchar(50)='', -- Bed 
    @Bathrooms          varchar(50)='', -- Bath 
    @GarageSqft         varchar(50)='', --GarageSqft 
    @Pool               varchar(50)='', --Pool 
    @Spa                varchar(50)='', --Spa 
    @AuctionDate        varchar(75)='', --Auction Date 
    @ListingStatus      varchar(50)='', --Listing Status 
    @EstimatedBid       varchar(50)='', --Estimated Bid 
    @TsOpeningBid       varchar(50)='', --Ts OpeningBid 
    @ApnOpeningBid      varchar(50)='', --ApnOpeningBid 
    @MaxBid             varchar(50)='', --Max Bid 
    @EstVal             varchar(50)='', --Estimate Value 
    @Tax                varchar(50)='', --Tax 
    @Nos                varchar(50)='', --Nos 
    @Liens              varchar(500)='', --Liens
    @Site               varchar(100)='', --Site 
    @PropertyDetails    varchar(2000)='', --Property Details
    @DocumentNumber     varchar(75)='', --Doc Number 
    @DateRecorded       varchar(75)='', -- Date Rec 
    @LandUse            varchar(50)='', -- Land Use
    @AssessorDesc       varchar(200)='', -- Assessor Desc
    @Phase              int,
	@Client             varchar(50)='',
	@CloseDate          varchar(50)='',
	@EscrowCompany      varchar(50)='',
	@MiscNotes          varchar(2000)='',
	@PrelistStatus      varchar(50)='',
	@PurchasePrice      varchar(50)='',
	@RehabCosts         varchar(50)='',
	@Source             varchar(50)='',
	@investorName		varchar(50)='',
	@DateAccept         varchar(50)='',
	@EstimateSalePrice MONEY = 0,
	@EstimatedResale MONEY = 0
)

AS

BEGIN
        set @OnOff = UPPER(@OnOff)
        set @Stories = UPPER(@Stories)
        
        INSERT INTO LvReo_ParcellAppraisal(
                                           Apn, LotSize, ConstructYear, LandUse, TotalSqft, Stories,
                                           Bedrooms, Bathrooms, GarageSqft, Pool, Spa, AssessorDesc
                                          )
        
        VALUES(@Apn, @LotSize, @ConstructYear, @LandUse, @TotalSQft, @Stories, @Bedrooms, @Bathrooms, @GarageSqft, @Pool,
               @Spa, @AssessorDesc)
               
               
        INSERT INTO LvReo_MasterList(
                                      OnOff, Trustee, TsNumber, Apn, PropertyLocation, ZipCode, vo, Hoa, AuctionDate,
                                      ListingStatus, EstimatedBid, TsOpeningBid, ApnOpeningBid, MaxBid, EstVal, Tax, 
                                      Nos, Liens, Site, PropertyDetails, DocumentNumber, DateRecorded, Phase,
			Client, CloseDate, EscrowCompany, MiscNotes, PrelistStatus, PurchasePrice, RehabCosts, Source,
            InvestorId, DateAccept, EstimateSalePrice, EstimatedResale) 
        VALUES (@OnOff, @Trustee, @TsNumber, @Apn, @PropertyLocation, @ZipCode, @VO, @HOA, @AuctionDate,
				@ListingStatus, @EstimatedBid,
                @TsOpeningBid, @ApnOpeningBid, @MaxBid, @EstVal, @Tax, @Nos, @Liens, @Site, @PropertyDetails,
                @DocumentNumber, @DateRecorded, @Phase,
                @Client, @CloseDate, @EscrowCompany, @MiscNotes, @PrelistStatus, @PurchasePrice, @RehabCosts, @Source,
                @investorName, @DateAccept, @EstimateSalePrice, @EstimatedResale)

END
GO